
--region Drop Existing Procedures

IF OBJECT_ID(N'[dbo].[Transfers_Insert]') IS NOT NULL
	DROP PROCEDURE [dbo].[Transfers_Insert]

IF OBJECT_ID(N'[dbo].[Transfers_Update]') IS NOT NULL
	DROP PROCEDURE [dbo].[Transfers_Update]

IF OBJECT_ID(N'[dbo].[Transfers_Delete]') IS NOT NULL
	DROP PROCEDURE [dbo].[Transfers_Delete]

IF OBJECT_ID(N'[dbo].[Transfers_Select]') IS NOT NULL
	DROP PROCEDURE [dbo].[Transfers_Select]

--endregion

GO


--region [dbo].[Transfers_Select]

-- Create By: vdev124
-- Date Generated: Friday, March 04, 2011

CREATE PROCEDURE [dbo].[Transfers_Select]
	@Page int = null,
	@PageSize int = null,
    @SortBy nvarchar(255) = null,
	@SortType bit = null,
	@IdTransfer int = null,
	@IdPlayer int = null,
	@IdClubFrom int = null,
	@IdClubTo int = null,
	@TransferDate datetime = null,
	@Price int = null,
	@Method nvarchar(100) = null
AS

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

Declare @RowCount int  
Set @RowCount = (
					Select Count (*) 
					From [dbo].[Transfers]
					WHERE
					(
						(@IdTransfer is null OR [Transfers].[IdTransfer] = @IdTransfer)
						AND (@IdPlayer is null OR [Transfers].[IdPlayer] = @IdPlayer)
						AND (@IdClubFrom is null OR [Transfers].[IdClubFrom] = @IdClubFrom)
						AND (@IdClubTo is null OR [Transfers].[IdClubTo] = @IdClubTo)
						AND (@TransferDate is null OR [Transfers].[TransferDate] = @TransferDate)
						AND (@Price is null OR [Transfers].[Price] = @Price)
						AND (@Method is null OR [Transfers].[Method] like @Method)
					)
				)
if(@Page is null)
begin
	Set @Page = 1
end

if(@PageSize is null)
begin
	Set @PageSize = @RowCount
end

if(@SortBy is null)
begin
	Set @SortBy = 'DBNull'
end

if(@SortType is null)
begin
	Set @SortType = 1
end

SELECT *
FROM   (
			SELECT [dbo].[Transfers].*,
                   ROW_NUMBER() OVER 
                   (ORDER BY
                        CASE WHEN @SortBy = 'DBNull' THEN NewID() END ASC,
						CASE WHEN @SortBy = 'IdTransfer' and @SortType = 1 THEN [Transfers].[IdTransfer] END ASC,
						CASE WHEN @SortBy = 'IdTransfer' and @SortType = 0 THEN [Transfers].[IdTransfer] END DESC,
						CASE WHEN @SortBy = 'IdPlayer' and @SortType = 1 THEN [Transfers].[IdPlayer] END ASC,
						CASE WHEN @SortBy = 'IdPlayer' and @SortType = 0 THEN [Transfers].[IdPlayer] END DESC,
						CASE WHEN @SortBy = 'IdClubFrom' and @SortType = 1 THEN [Transfers].[IdClubFrom] END ASC,
						CASE WHEN @SortBy = 'IdClubFrom' and @SortType = 0 THEN [Transfers].[IdClubFrom] END DESC,
						CASE WHEN @SortBy = 'IdClubTo' and @SortType = 1 THEN [Transfers].[IdClubTo] END ASC,
						CASE WHEN @SortBy = 'IdClubTo' and @SortType = 0 THEN [Transfers].[IdClubTo] END DESC,
						CASE WHEN @SortBy = 'TransferDate' and @SortType = 1 THEN [Transfers].[TransferDate] END ASC,
						CASE WHEN @SortBy = 'TransferDate' and @SortType = 0 THEN [Transfers].[TransferDate] END DESC,
						CASE WHEN @SortBy = 'Price' and @SortType = 1 THEN [Transfers].[Price] END ASC,
						CASE WHEN @SortBy = 'Price' and @SortType = 0 THEN [Transfers].[Price] END DESC,
						CASE WHEN @SortBy = 'Method' and @SortType = 1 THEN [Transfers].[Method] END ASC,
						CASE WHEN @SortBy = 'Method' and @SortType = 0 THEN [Transfers].[Method] END DESC                   
                    ) AS RowNumber 
			FROM [dbo].[Transfers]
			Where 
			(
				(@IdTransfer is null OR [Transfers].[IdTransfer] = @IdTransfer)
				AND (@IdPlayer is null OR [Transfers].[IdPlayer] = @IdPlayer)
				AND (@IdClubFrom is null OR [Transfers].[IdClubFrom] = @IdClubFrom)
				AND (@IdClubTo is null OR [Transfers].[IdClubTo] = @IdClubTo)
				AND (@TransferDate is null OR [Transfers].[TransferDate] = @TransferDate)
				AND (@Price is null OR [Transfers].[Price] = @Price)
				AND (@Method is null OR [Transfers].[Method] like @Method)
			)
		) AS Temp
WHERE  RowNumber Between (@Page * @PageSize - @PageSize + 1) And @Page * @PageSize
Return @RowCount


--end [dbo].[Transfers_Select]
--endregion

GO
--=========================================================================================--

	

--region [dbo].[Transfers_Insert]

-- Create By: vdev124
-- Date Generated: Friday, March 04, 2011

CREATE PROCEDURE [dbo].[Transfers_Insert]
	@IdTransfer int OUTPUT,
	@IdPlayer int,
	@IdClubFrom int,
	@IdClubTo int,
	@TransferDate datetime,
	@Price int,
	@Method nvarchar(100)

AS


INSERT INTO [dbo].[Transfers] 
(
	[IdPlayer],
	[IdClubFrom],
	[IdClubTo],
	[TransferDate],
	[Price],
	[Method]
)
VALUES 
(
	@IdPlayer,
	@IdClubFrom,
	@IdClubTo,
	@TransferDate,
	@Price,
	@Method
)

SET @IdTransfer = SCOPE_IDENTITY()

--end [dbo].[Transfers_Insert]
--endregion

GO
--=========================================================================================--

--region [dbo].[Transfers_Update]

-- Create By: vdev124
-- Date Generated: Friday, March 04, 2011

CREATE PROCEDURE [dbo].[Transfers_Update]
	@IdTransfer int,
	@IdPlayer int,
	@IdClubFrom int,
	@IdClubTo int,
	@TransferDate datetime,
	@Price int,
	@Method nvarchar(100)
AS


UPDATE [dbo].[Transfers] SET
	[IdPlayer] = @IdPlayer,
	[IdClubFrom] = @IdClubFrom,
	[IdClubTo] = @IdClubTo,
	[TransferDate] = @TransferDate,
	[Price] = @Price,
	[Method] = @Method
WHERE
	[IdTransfer] = @IdTransfer

--end [dbo].[Transfers_Update]
--endregion

GO
--=========================================================================================--

--region [dbo].[Transfers_Delete]

-- Create By: vdev124
-- Date Generated: Friday, March 04, 2011

CREATE PROCEDURE [dbo].[Transfers_Delete]
	@IdTransfer int
AS


DELETE FROM [dbo].[Transfers]
WHERE
(
	[IdTransfer] = @IdTransfer
)

--end [dbo].[Transfers_Delete]
--endregion

GO
--=========================================================================================--

